from django.shortcuts import render
import pymysql
from django.http import JsonResponse, StreamingHttpResponse
from django.shortcuts import HttpResponse, render
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage, InvalidPage
import json
import requests
import time

from .db_set  import db_data
db_data = db_data


def db_login():
    db = pymysql.connect(db_data['server'], db_data['user_name'], db_data['pwd'], db_data['db'], charset='utf8')
    return db


def show_admin(request):
    response = {}
    try:
        db = db_login()
        cursor = db.cursor()
        sql = "select * from admin"
        cursor.execute(sql)
        res = cursor.fetchall()
        db.commit()
        cursor.close()
        db.close()
        response['list'] = res
        response['msg'] = 'success'
        response['error_num'] = 0
    except Exception as e:
        response['msg'] = str(e)
        response['error_num'] = 1
    return JsonResponse(response)


def token(user_name, pass_word):
    data = {
        'username': (None, user_name),
        'password': (None, pass_word),
    }
    # url = "http://wmss.a2milkvip.com/api-token-auth/"
    # url = "http://127.0.0.1:8006/api-token-auth/"
    url = "http://wmss.jasonsong.top/api-token-auth/"
    res = requests.post(url=url, files=data, verify=False, timeout=10)
    return res.text


def login(request):
    if request.method == "POST":
        # print()
        try:
            msg = json.loads(request.body)
            # print(msg)
            user = msg["username"]
            pwd = msg["password"]
            tok = json.loads(token(user, pwd))["token"]
            toke = json.dumps(tok)[1:-1]
            # print(toke,type(toke))
            msg = {
                "data": {"token": toke,},
                "message": "账号密码验证成功",
                "code": 20000,
                'roles': ['admin'],
            }

            # print(msg)
            return JsonResponse(msg, safe=False)
        except:
            res = {'code': 'no', 'message': '账号或密码错误'}
            return JsonResponse(res, safe=False)


def user_info(request):
    try:
        msg = {
            'code': 20000,
            'data': {
                             'roles': ['admin'],
                             'introduction': 'I am a super administrator',
                             'avatar': 'https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif',
                             'name': 'Super Admin'
                         },
        }
        return JsonResponse(msg, safe=False)
    except:
        res = {'code': 'no', 'message': 'user_info失败'}
        return JsonResponse(res, safe=False)


def logout(request):
    msg = {
        'code': 20000,
        'data': 'success'
      }
    return JsonResponse(msg, safe=False)


def product_list(request):
    db = db_login()
    cursor = db.cursor()
    sql = 'select id,product_name,brand,ex from wms_product'
    cursor.execute(sql)
    res = cursor.fetchall()
    cursor.close()
    db.close()
    lst = []
    for i in res:
        lst.append({'id':i[0],'product_name':i[1],'brand':i[2],'ex':i[3]})
    msg = {
        'code': 20000,
        'data': 'success',
        'options': res
      }
    return JsonResponse(msg, safe=False)


def warehouse_list(request):
    db = db_login()
    cursor = db.cursor()
    sql = 'select id,warehouse from wms_warehouse'
    cursor.execute(sql)
    res = cursor.fetchall()
    cursor.close()
    db.close()
    lst = []
    for i in res:
        lst.append({'value':i[0],'label':i[1]})
    msg = {
        'code': 20000,
        'data': 'success',
        'options': res
      }
    return JsonResponse(msg, safe=False)


def subinbound(request):
    if request.method == "POST":
        res = json.loads(request.body)
        # print(res)
        inbound_no = res["inbound_no"]
        patch_no = res["patch_no"]
        product_list = res["products"]
        warehouse = res["warehouse"]
        tm = round(time.time())
        lst = []
        for i in product_list:
            if i["amount"] != 0 or i["value"] != '':
                # print(i["bf"])
                lst.append((inbound_no,patch_no,i["value"],i["price"],i["amount"],tm,'0',warehouse))
        # print(lst)
        db = db_login()
        cursor = db.cursor()
        sql = "insert into wms_inbound_record(inbound_no,patch_no,product_name,price,amount,timestamp,amount_act,warehouse) VALUES (%s,%s,%s,%s,%s,'%s',%s,%s)"
        cursor.executemany(sql,lst)
        db.commit()
        cursor.close()
        db.close()
        msg = {
            'code': 20000,
            'data': 'success',
          }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)

def inbound_record(request):
    if request.method == "GET":
        db = db_login()
        cursor = db.cursor()
        sql = "SELECT id,inbound_no,patch_no,product_name,bf,amount,FROM_UNIXTIME(TIMESTAMP,'%Y/%m/%d'),amount_act,warehouse FROM wms_inbound_record WHERE amount<> amount_act ORDER BY id DESC"
        cursor.execute(sql)
        lst = cursor.fetchall()
        cursor.close()
        db.close()
        dic = {}
        for i in lst:
            if i[1] in dic.keys():
                dic[i[1]].append(i)
            else:
                dic[i[1]] = [i]
        fin_lst = []
        jishu = 0
        for key in dic:
            jishu += 1
            fin_lst.append(
                {
                 "id":jishu,
                 "inbound_no": key,
                 "patch_no": dic[key][0][2],
                 "warehouse" :dic[key][0][8],
                 "product_name" : '',
                 "bf" : '',
                 "amount" : 0,
                 "timestamp" : dic[key][0][6],
                 "amount_act":0,
                 "children":[],
                }
            )
            if len(dic[key]) == 1:
                fin_lst[-1]["product_name"] = dic[key][0][3]
                fin_lst[-1]["bf"] = dic[key][0][4]
                fin_lst[-1]["amount"] = dic[key][0][5]
                fin_lst[-1]["timestamp"] = dic[key][0][6]
                fin_lst[-1]["amount_act"] = dic[key][0][7]
                del fin_lst[-1]["children"]
            elif len(dic[key]) > 1:
                jishu_2 = 0
                for  m in dic[key]:
                    jishu_2 +=1
                    fin_lst[-1]["children"].append(
                            {
                               "id" : int(str(jishu) + str(jishu_2)),
                               "inbound_no": key,
                                "patch_no": dic[key][0][2],
                                "product_name" : m[3],
                                "bf" : m[4],
                                "amount" : m[5],
                                "timestamp" : m[6],
                                "amount_act" : m[7],
                            }
                    )
                for  n in fin_lst[-1]["children"]:
                    fin_lst[-1]["amount"] += int(n['amount'])
                    fin_lst[-1]["amount_act"] += int(n['amount_act'])
        msg = {
                'code': 20000,
                'data': 'success',
                "list":fin_lst,
              }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def check_inbound_details(request):
    if request.method == "POST":
        res = json.loads(request.body)
        inbound_no = res["inbound_no"]
        db = db_login()
        cursor = db.cursor()
        sql = "select id,inbound_no,patch_no,product_name,bf,amount,FROM_UNIXTIME(timestamp,'%Y/%m/%d'),amount_act from wms_inbound_record where inbound_no = '{0}'" .format(inbound_no)
        cursor.execute(sql)
        lst = cursor.fetchall()
        cursor.close()
        db.close()
        li = []
        for i in lst:
            da = { 'key':i[0], 'value':i[3],'bf':i[4],'amount_act':int(i[7]),'amount':int(i[5])}
            li.append(da)
        # print(li)
        msg = {
                'code': 20000,
                'data': 'success',
                'list':li,
              }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def actinbound(request):
    if request.method == "POST":
        res = json.loads(request.body)
        inbound_no = res["inbound_no"]
        pro_list = res["products"]
        tm = round(time.time())
        db = db_login()
        cursor = db.cursor()
        sql = "select id,inbound_no,patch_no,product_name,bf,amount,FROM_UNIXTIME(timestamp,'%Y/%m/%d'),amount_act,warehouse from wms_inbound_record where inbound_no = '{0}'" .format(inbound_no)
        cursor.execute(sql)
        lst = cursor.fetchall()
        sql_pro = "select warehouse,product_name,qty from wms_stock"
        cursor.execute(sql_pro)
        sql_pro_list = cursor.fetchall()
        patch_no = lst[0][2]
        warehouse = lst[0][8]
        cursor.close()
        db.close()
        sum_list = []
        refresh_product_list = []
        add_product_list = []
        add_sum = []
        add_sum_list = []
        stock_add = []

        for i in lst:
            sum_list.append(i[0])
        for i in sql_pro_list:
            add_sum_list.append(i[0]+i[1])
        for k in pro_list:
            if k["key"] in sum_list:
                refresh_product_list.append((k['value'],k['bf'],k['amount_act'],k["key"]))
                for i in lst:
                    if k["key"] == i[0]:
                        add_sum.append(
                                (warehouse,0,k['value'],int(k['amount_act'])-int(i[7]))
                        )
            else:
                add_product_list.append((inbound_no,patch_no,k['value'],k['bf'],'0',tm,k['amount_act'],warehouse))
                add_sum.append(
                                (warehouse,0,k['value'],k['amount_act'])
                        )
        stock_add_new = []
        stock_ad_new = {}
        for i in add_sum:
            if i[0]+i[2] in add_sum_list:
                stock_add.append((i[3],i[2],i[0]))
            else:
                if i[2] not in stock_ad_new.keys():
                    stock_ad_new[i[2]]=[i[0],i[1],i[2],i[3]]
                else:
                    stock_ad_new[i[2]][3] += i[3]


        for key in stock_ad_new.keys():
            stock_add_new.append(
                stock_ad_new[key]
            )


        if refresh_product_list != []:
            db = db_login()
            cursor = db.cursor()
            cursor.executemany("update wms_inbound_record Set product_name = %s,bf=%s,amount_act=%s where id = %s",refresh_product_list)
            cursor.executemany("update wms_stock Set qty = qty+%s where product_name = %s and warehouse = %s",stock_add)
            db.commit()
            cursor.close()
            db.close()
        if add_product_list != []:
            db = db_login()
            cursor = db.cursor()
            sql_add = "insert into wms_inbound_record(inbound_no,patch_no,product_name,bf,amount,timestamp,amount_act,warehouse) VALUES (%s,%s,%s,%s,%s,'%s',%s,%s) "
            cursor.executemany(sql_add,add_product_list)
            db.commit()
            cursor.close()
            db.close()
        if stock_add_new != []:
            db = db_login()
            cursor = db.cursor()
            sql_add_new = "insert into wms_stock(warehouse,product_id,product_name,qty) VALUES (%s,%s,%s,%s) "
            cursor.executemany(sql_add_new,stock_add_new)
            cursor.execute("UPDATE wms_stock a join wms_product b on a.product_name= b.product_name set a.product_id = b.id")
            db.commit()
            cursor.close()
            db.close()
        msg = {
                'code': 20000,
                'data': 'success',
              }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def add_warehouse(request):
    if request.method == "POST":
        res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        sql = "INSERT INTO wms_warehouse(warehouse) VALUES('{0}')".format(res["data"])
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)

def add_goods(request):
    if request.method == "POST":
        res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        sql = "INSERT INTO wms_product(product_name,brand,ex) VALUES('{0}','{1}','{2}')".format(res["product_name"],res["brand"],res["ex"])
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def agency_list(request):
    db = db_login()
    cursor = db.cursor()
    sql = 'select id,agency,ex from wms_agency'
    cursor.execute(sql)
    res = cursor.fetchall()
    cursor.close()
    db.close()
    lst = []
    for i in res:
        lst.append({'id':i[0],'agency':i[1],'ex':i[2]})
    msg = {
        'code': 20000,
        'data': 'success',
        'options': res
      }
    return JsonResponse(msg, safe=False)


def add_agency(request):
    if request.method == "POST":
        res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        sql = "INSERT INTO wms_agency(agency,ex) VALUES('{0}','{1}')".format(res["agency"],res["ex"])
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def check_outbound_details(request):
    if request.method == "POST":
        res = json.loads(request.body)
        # print(res)
        warehouse = res["warehouse"]
        db = db_login()
        cursor = db.cursor()
        # sql = "select * from wms_stock where warehouse = '{0}'".format(warehouse)
        sql = 'select id,product_name,brand,ex from wms_product'
        # sql = "SELECT a.id,a.inbound_no,a.product_name,a.bf,a.amount-IFNULL(c.invoice_amount,0) as left_stock,a.warehouse from wms_inbound_record a left join(SELECT b.con_id,SUM(b.amount)as invoice_amount from wms_invoice b GROUP BY b.con_id) c on c.con_id = a.id where warehouse = '{0}' and a.amount-IFNULL(c.invoice_amount,0)<>0".format(warehouse)
        cursor.execute(sql)
        lis = cursor.fetchall()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'options':lis,
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def suboutbound(request):
    if request.method == "POST":
        tm = round(time.time())
        res = json.loads(request.body)
        # print(res)
        list = res['invoice']
        # print(list)
        ls = []
        out_record = []
        for i in list:
            if int(i["amount"])>0:
                # ls.append((int(i["amount"]),i["id"]))
                out_record.append((res["outbound_no"],i["value"],i["amount"],i["price"],tm,res["agency"],'1',res["company"]))
        db = db_login()
        cursor = db.cursor()
        # print(out_record)
        # cursor.executemany("update wms_stock set qty = qty-%s where id = %s",ls)
        # cursor.executemany("insert into wms_outbound_record(outbound_no,warehouse,stock_id,product_name,amount,timestamp,agency,price,total,bf,status) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",out_record)
        cursor.executemany("insert into wms_invoice(invoice_no,con_id,amount,price,timestamp,agency,status,company) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",out_record)

        db.commit()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def outbound_record(request):
    if request.method == "GET":
        db = db_login()
        cursor = db.cursor()
        # sql = "SELECT id,outbound_no,warehouse,stock_id,product_name,amount,FROM_UNIXTIME(TIMESTAMP,'%Y/%m/%d'),agency,price,total,bf,CASE STATUS WHEN 1 THEN '正常' WHEN 2 THEN '已出库' END AS 'status' FROM wms_outbound_record WHERE STATUS>0 order by TIMESTAMP DESC"
        sql = "SELECT d.agency,d.amount-IFNULL(e.amount,0) as peihuo FROM (SELECT agency,SUM(c.amount) as amount from wms_invoice c  GROUP BY c.agency) d left JOIN (SELECT b.agency,SUM(a.amount)as amount from wms_peihuo a  join wms_invoice b on b.id = a.invoice_id  GROUP BY b.agency)e on d.agency = e.agency where d.amount-IFNULL(e.amount,0) <>0"
        cursor.execute(sql)
        lis = cursor.fetchall()
        cursor.close()
        db.close()
        res = []
        for i in lis:
            # res.append(
            #         {
            #             "id":i[0],
            #             "outbound_no":i[1],
            #             "warehouse":i[2],
            #             "stock_id":i[3],
            #             "product_name":i[4],
            #             "amount":i[5],
            #             "timestamp":i[6],
            #             "agency":i[7],
            #             "price":i[8],
            #             "total":i[9],
            #             "bf":i[10],
            #             "status":i[11],
            #         }
            # )
            res.append({
                "agency": i[0],
                "peihuo":i[1],
            }

            )
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'list':res,
                  }
        # print(res)
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def stock_list(request):
    db = db_login()
    cursor = db.cursor()
    sql = 'select id,warehouse,product_name,qty from wms_stock order by concat(warehouse,product_id)'
    cursor.execute(sql)
    res = cursor.fetchall()
    cursor.close()
    db.close()
    lst = []
    for i in res:
        lst.append({'id':i[0],'product_name':i[2],'warehouse':i[1],'qty':i[3]})
    msg = {
        'code': 20000,
        'data': 'success',
        'options': res
      }
    return JsonResponse(msg, safe=False)


def del_outbound(request):
    if request.method == "POST":
        res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        sql_del = "select amount,stock_id from wms_outbound_record where id  ={0}".format(res)
        cursor.execute(sql_del)
        lis = cursor.fetchone()
        sql_del_stock = "update wms_stock set qty = qty+{0} where id = {1} ".format(lis[0],lis[1])
        cursor.execute(sql_del_stock)
        sql = "update wms_outbound_record set status = 0 where id = {0}".format(res)
        cursor.execute(sql)
        db.commit()
        sql_new = "SELECT id,outbound_no,warehouse,stock_id,product_name,amount,FROM_UNIXTIME(TIMESTAMP,'%Y/%m/%d'),agency,price,total,bf,CASE STATUS WHEN 1 THEN '正常' WHEN 2 THEN '已出库' END AS 'status' FROM wms_outbound_record WHERE STATUS>0"
        cursor.execute(sql_new)
        liss = cursor.fetchall()
        cursor.close()
        db.close()
        rest = []
        for i in liss:
            rest.append(
                    {
                        "id":i[0],
                        "outbound_no":i[1],
                        "warehouse":i[2],
                        "stock_id":i[3],
                        "product_name":i[4],
                        "amount":i[5],
                        "timestamp":i[6],
                        "agency":i[7],
                        "price":i[8],
                        "total":i[9],
                        "bf":i[10],
                        "status":i[11],
                    }
            )
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'list':rest,
                  }

        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def check_actstock(request):
    if request.method == "GET":
        db = db_login()
        cursor = db.cursor()
        # sql = 'select id,product_name,brand,ex from wms_product'
        sql = "SELECT c.id,c.warehouse,c.product_name,IFNULL(c.amount,0)-IFNULL(d.amount,0),c.bf FROM (SELECT b.warehouse,b.product_name,sum( b.amount_act ) AS 'amount',b.bf,CONCAT(b.warehouse,b.product_name,b.bf) AS 'id' FROM wms_inbound_record b GROUP BY CONCAT(b.warehouse,b.product_name,b.bf)) c LEFT JOIN (SELECT a.warehouse,a.product_name,SUM(a.amount) AS 'amount',a.bf,CONCAT(a.warehouse,a.product_name,a.bf) AS 'id' FROM wms_outbound_record a where a.`status`>0 GROUP BY CONCAT(a.warehouse,a.product_name,a.bf)) d ON c.id=d.id WHERE IFNULL(c.amount,0)-IFNULL(d.amount,0)<>0"
        cursor.execute(sql)
        lis = cursor.fetchall()
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'options':lis,
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def re_patch_no(request):
    res = json.loads(request.body)
    # print(res)
    db = db_login()
    cursor = db.cursor()
    sql = "update wms_inbound_record SET patch_no = '{0}'  where inbound_no = '{1}'".format(res['patch_no'],res['inbound_no'])
    cursor.execute(sql)
    db.commit()
    cursor.close()
    db.close()
    msg = {
        "code": 20000,
    }
    return JsonResponse(msg, safe=False)


def peihuo(request):
    res = json.loads(request.body)
    # res = request
    agency = res["data"]
    db = db_login()
    cursor = db.cursor()
    sql = "SELECT b.id,b.invoice_no,e.product_name,b.amount-IFNULL(d.amount,0) AS daipeihuo,b.agency,b.STATUS FROM wms_invoice b LEFT JOIN (SELECT a.id,a.invoice_id,SUM(a.amount) AS amount,a.con_id,a.TIMESTAMP FROM wms_peihuo a GROUP BY a.invoice_id) d ON b.id=d.invoice_id LEFT JOIN (SELECT id,product_name FROM wms_product) e ON e.id=b.con_id WHERE b.amount-IFNULL(d.amount,0)<> 0 and b.agency = '{0}'".format(agency)
    # print(sql)
    cursor.execute(sql)
    re = cursor.fetchall()
    # print(re)
    cursor.close()
    db.close()
    rest = []
    for i in re:
        rest.append(
            {
                "id": i[0],
                "invoice_no": i[1],
                "product_name": i[2],
                "daipeihuo": i[3],
                "agency": i[4],
                "status": i[5],
                "con_id":'',
                "amount":0,

            }
        )
    msg = {
        "code": 20000,
        "data":rest,
    }
    return JsonResponse(msg, safe=False)


def check_peihuo_product(request):
    if request.method == "POST":
        res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        # sql = "select * from wms_stock where warehouse = '{0}'".format(warehouse)
        sql = "SELECT a.id,a.inbound_no,a.product_name,a.bf,a.amount_act-IFNULL(c.peihuo_amount,0) AS left_stock,a.warehouse FROM wms_inbound_record a LEFT JOIN (SELECT b.con_id,SUM(b.amount) AS peihuo_amount FROM wms_peihuo b GROUP BY b.con_id) c ON c.con_id=a.id where a.product_name = '{0}' and a.amount_act-IFNULL(c.peihuo_amount,0) <> 0".format(res['product'])
        cursor.execute(sql)
        lis = cursor.fetchall()
        # print(lis)
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'options':lis,
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def submit_peihuo_product(request):
    if request.method == "POST":
        res = json.loads(request.body)
        tm = round(time.time())
        ls = []
        for i in res:
            if i["amount"] != 0:
                ls.append((i["id"],i["amount"],i["con_id"],tm))
        # print(ls)
        db = db_login()
        cursor = db.cursor()
        sql = "insert into wms_peihuo(invoice_id,amount,con_id,timestamp) VALUES (%s,%s,%s,%s)"
        cursor.executemany(sql,ls)
        lis = cursor.fetchall()
        db.commit()
        cursor.close()
        db.close()
        msg = {
            'code': 20000,
            'data': 'success',
            'options': lis,
        }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
        }
        return JsonResponse(msg, safe=False)


# 入库-已开票、采购-已开票
def peihuo_goods(request):
    if request.method == "POST":
        # res = json.loads(request.body)
        db = db_login()
        cursor = db.cursor()
        # sql = "select * from wms_stock where warehouse = '{0}'".format(warehouse)
        sql = "SELECT a.id,a.inbound_no,a.product_name,a.bf,a.amount_act AS stock,a.warehouse,IFNULL(d.amount,0),IFNULL(n.amount,0),a.amount_act-IFNULL(d.amount,0) AS left_stock,n.amount-IFNULL(d.amount,0) AS lilun_left_stock FROM (SELECT e.id,e.inbound_no,e.patch_no,e.product_name,e.price,SUM(e.amount) AS 'amount',e.`timestamp`,SUM(e.amount_act) AS 'amount_act',e.warehouse,e.bf FROM wms_inbound_record e GROUP BY e.product_name) a LEFT JOIN (SELECT b.con_id,SUM(b.amount) AS 'amount',c.product_name FROM wms_invoice b JOIN wms_product c ON c.id=b.con_id GROUP BY b.con_id) d ON d.product_name=a.product_name JOIN (SELECT m.product_name,SUM(m.amount) AS 'amount' FROM wms_inbound_record m WHERE m.product_name<> '操作费' GROUP BY m.product_name ORDER BY m.product_name) n ON n.product_name=a.product_name WHERE a.amount_act-IFNULL(d.amount,0)<> 0 AND a.product_name<> '操作费' ORDER BY a.product_name;"
        cursor.execute(sql)
        lis = cursor.fetchall()
        # print(lis)
        cursor.close()
        db.close()
        msg = {
                    'code': 20000,
                    'data': 'success',
                    'options':lis,
                  }
        return JsonResponse(msg, safe=False)
    else:
        msg = {
            'code': 50000,
            'data': 'failure',
          }
        return JsonResponse(msg, safe=False)


def company_list(request):
    db = db_login()
    cursor = db.cursor()
    sql = 'select id,company from wms_company'
    cursor.execute(sql)
    res = cursor.fetchall()
    cursor.close()
    db.close()
    lst = []
    for i in res:
        lst.append({'id': i[0], 'agency': i[1]})
    msg = {
        'code': 20000,
        'data': 'success',
        'options': res
    }
    return JsonResponse(msg, safe=False)


def dashboard_outbound(request):
    db = db_login()
    cursor = db.cursor()
    sql = "SELECT b.id,b.invoice_no,e.product_name,b.amount-IFNULL(d.amount,0) AS daipeihuo,b.agency,b.STATUS FROM wms_invoice b LEFT JOIN (SELECT a.id,a.invoice_id,SUM(a.amount) AS amount,a.con_id,a.TIMESTAMP FROM wms_peihuo a GROUP BY a.invoice_id) d ON b.id=d.invoice_id LEFT JOIN (SELECT id,product_name FROM wms_product) e ON e.id=b.con_id WHERE b.amount-IFNULL(d.amount,0)<> 0 ORDER BY CONCAT(b.agency,b.invoice_no)"
    # print(sql)
    cursor.execute(sql)
    re = cursor.fetchall()
    # print(re)
    cursor.close()
    db.close()
    rest = []
    for i in re:
        rest.append(
            {
                "id": i[0],
                "invoice_no": i[1],
                "product_name": i[2],
                "daipeihuo": i[3],
                "agency": i[4],
                "status": i[5],
                "con_id": '',
                "amount": 0,

            }
        )
    msg = {
        "code": 20000,
        "data": rest,
    }
    return JsonResponse(msg, safe=False)


def check_payment(request):
    db = db_login()
    cursor = db.cursor()
    sql = "SELECT a.company,a.invoice_no,a.total,d.yifu,a.agency FROM (SELECT b.invoice_no,SUM(b.amount*b.price) AS total,b.company,b.agency FROM wms_invoice b GROUP BY CONCAT(b.invoice_no,b.agency)) a LEFT JOIN (SELECT*FROM (SELECT d.invoice_no,SUM(d.total) AS yifu FROM wms_check_payment d GROUP BY d.invoice_no) c) d ON a.invoice_no=d.invoice_no where a.total-IFNULL(d.yifu,0)<>0"
    # print(sql)
    cursor.execute(sql)
    re = cursor.fetchall()
    # print(re)
    cursor.close()
    db.close()
    rest = []
    for i in re:
        rest.append(
            {
                "company": i[0],
                "invoice_no": i[1],
                "total": i[2],
                "yifu": i[3],
                "agency": i[4],
            }
        )
    msg = {
        "code": 20000,
        "data": rest,
    }
    return JsonResponse(msg, safe=False)


def submit_payment(request):
    res = json.loads(request.body)
    invoice_no = res["invoice_no"]
    total = res["total"]
    tm = round(time.time())
    list = [(invoice_no,total,tm)]
    # print(res)
    db = db_login()
    cursor = db.cursor()
    sql = "insert into wms_check_payment(invoice_no,total,timestamp ) VALUES (%s,%s,%s)"
    # print(sql)
    cursor.executemany(sql,list)
    # re = cursor.fetchall()
    # print(re)
    db.commit()
    cursor.close()
    db.close()
    msg = {
        "code": 20000,

    }
    return JsonResponse(msg, safe=False)


def check_invoice_no(request):
    db = db_login()
    cursor = db.cursor()
    sql = "SELECT a.invoice_no,a.company,a.`timestamp`,a.agency,b.product_name,a.amount,a.price,a.id FROM wms_invoice a JOIN wms_product b ON b.id=a.con_id WHERE a.invoice_no='' "
    # print(sql)
    cursor.execute(sql)
    re = cursor.fetchall()
    # print(re)
    cursor.close()
    db.close()
    rest = []
    for i in re:
        rest.append(
            {
                "invoice_no": i[0],
                "company": i[1],
                "timestamp": i[2],
                "agency": i[3],
                "product_name": i[4],
                "amount": i[5],
                "price": i[6],
                "id": i[7],
            }
        )
    # print(rest)
    msg = {
        "code": 20000,
        "data": rest,
    }
    return JsonResponse(msg, safe=False)


def up_invoice_no(request):
    res = json.loads(request.body)
    # print(res)
    db = db_login()
    cursor = db.cursor()
    sql = "update wms_invoice set invoice_no = '{0}' where timestamp  = '{1}'".format(res['invoice_no'],res['timestamp'])
    # print(sql)
    cursor.execute(sql)
    db.commit()
    # print(re)
    cursor.close()
    db.close()
    # print(rest)
    msg = {
        "code": 20000,
        "message": "success",
    }
    return JsonResponse(msg, safe=False)

import openpyxl
import  datetime
import os
path = os.path.dirname(__file__)


from django.shortcuts import render,HttpResponse
from wms import settings
from django.utils.encoding import escape_uri_path
from django.http import StreamingHttpResponse
from django.http import JsonResponse
from rest_framework.views import APIView
from rest_framework import status
import os
class ExcelFileDownload(APIView):
    def post(self,request):
        # print(request.data)
        filename = "accountant.xlsx"
        date = request.data.get("filename")
        start_date = int(time.mktime(datetime.datetime.strptime(date[0][0:10], '%Y-%m-%d').timetuple()))
        end_date = int(time.mktime(datetime.datetime.strptime(date[1][0:10], '%Y-%m-%d').timetuple()))
        db = db_login()
        cursor = db.cursor()
        sql = "SELECT a.con_id,c.inbound_no,FROM_UNIXTIME(c.`timestamp`,'%Y/%m/%d') AS '采购时间','澳洲',b.agency,c.product_name,a.amount,b.price AS '售价',a.amount*b.price AS '销售总价',c.price AS '采购价',a.amount*c.price AS '采购金额',a.amount*b.price-a.amount*c.price AS '净利润',b.invoice_no,b.company,c.bf FROM wms_peihuo a LEFT JOIN wms_invoice b ON b.id=a.invoice_id LEFT JOIN (SELECT e.id,e.inbound_no,e.patch_no,e.product_name,f.price,e.amount,e.`timestamp`,e.amount_act,e.warehouse,e.bf FROM wms_inbound_record e LEFT JOIN (SELECT CONCAT(d.inbound_no,d.product_name) AS id,d.price FROM wms_inbound_record d GROUP BY CONCAT(d.inbound_no,d.product_name)) f ON f.id=CONCAT(e.inbound_no,e.product_name)) c ON c.id=a.con_id where b.`timestamp` > {0}  and b.`timestamp` < {1}".format(
            start_date, end_date)
        # print(sql)
        cursor.execute(sql)
        re = cursor.fetchall()
        # print(re)
        cursor.close()
        db.close()

        download_file_path = os.path.join(settings.BASE_DIR, "download", filename)
        # print("download_file_path",download_file_path)
        # response = HttpResponse(content_type='application/vnd.ms-excel')
        # response['Content-Disposition'] = "attachment;filename= accountant.xlsx"
        # wb = openpyxl.load_workbook(download_file_path)
        # ws = wb['Sheet1']
        # for i in range(0, len(re)):
        #     ws.cell(row=i + 2, column=1).value = re[i][1]
        #     ws.cell(row=i + 2, column=2).value = re[i][7]
        #     ws.cell(row=i + 2, column=3).value = re[i][6]
        #     ws.cell(row=i + 2, column=9).value = re[i][2]
        # wb.save(response)

        # response = self.big_file_download(download_file_path, filename)
        # response = StreamingHttpResponse(self.file_iterator(download_file_path))
        response = HttpResponse(content_type='application/vnd.ms-excel')
        # 增加headers
        response['Content-Type'] = 'application/octet-stream'
        response['Access-Control-Expose-Headers'] = "Content-Disposition, Content-Type"
        response['Content-Disposition'] = "attachment; filename={}".format(escape_uri_path(filename))
        wb = openpyxl.load_workbook(download_file_path)
        ws = wb['Sheet1']
        for i in range(0, len(re)):
            ws.cell(row=i + 2, column=1).value = re[i][1]
            ws.cell(row=i + 2, column=2).value = re[i][2]
            ws.cell(row=i + 2, column=3).value = re[i][3]
            ws.cell(row=i + 2, column=4).value = re[i][4]
            ws.cell(row=i + 2, column=5).value = re[i][5]
            ws.cell(row=i + 2, column=6).value = re[i][6]
            ws.cell(row=i + 2, column=7).value = re[i][7]
            ws.cell(row=i + 2, column=8).value = re[i][8]
            ws.cell(row=i + 2, column=9).value = re[i][9]
            ws.cell(row=i + 2, column=10).value = re[i][10]
            ws.cell(row=i + 2, column=11).value = re[i][11]
            ws.cell(row=i + 2, column=12).value = re[i][12]
            ws.cell(row=i + 2, column=13).value = re[i][13]
            ws.cell(row=i + 2, column=14).value = re[i][14]
        wb.save(response)
        # print(response)
        if response:
            return response
        return JsonResponse({'status': 'HttpResponse', 'msg': 'Excel下载失败'})

    def file_iterator(self,file_path, chunk_size=512):
        """
        文件生成器,防止文件过大，导致内存溢出
        :param file_path: 文件绝对路径
        :param chunk_size: 块大小
        :return: 生成器
        """
        with open(file_path, mode='rb') as f:
            while True:
                c = f.read(chunk_size)
                if c:
                    yield c
                else:
                    break

    def big_file_download(self,download_file_path, filename):
        try:
            response = StreamingHttpResponse(self.file_iterator(download_file_path))
            # 增加headers
            response['Content-Type'] = 'application/octet-stream'
            response['Access-Control-Expose-Headers'] = "Content-Disposition, Content-Type"
            response['Content-Disposition'] = "attachment; filename={}".format(escape_uri_path(filename))
            return response
        except Exception:
            return JsonResponse({'status': status.HTTP_400_BAD_REQUEST, 'msg': 'Excel下载失败'},
                                status=status.HTTP_400_BAD_REQUEST)

import datetime
import time

def stock_flow(request):
    db = db_login()
    cursor = db.cursor()
    sql_inbound = "SELECT product_name,amount_act,`timestamp` from wms_inbound_record where product_name <> '操作费';"
    sql_invoice = "SELECT b.product_name,a.amount,a.`timestamp` from wms_invoice a join wms_product b on b.id = a.con_id where b.product_name <> '操作费';"
    sql_peihuo = "SELECT c.product_name,a.amount,a.`timestamp` FROM wms_peihuo a JOIN wms_invoice b ON b.id=a.invoice_id JOIN wms_product c ON c.id=b.con_id WHERE c.product_name<> '操作费';"

    # print(sql)
    cursor.execute(sql_inbound)
    res_inbound = list(cursor.fetchall())
    cursor.execute(sql_peihuo)
    res_invoice = list(cursor.fetchall())
    # print(re)

    cursor.close()
    db.close()
    # print(rest)
    # print(res_inbound)
    # print(res_invoice)
    start_timestamp = 1640966400
    one_week = 604800
    now_timestamp = int(time.mktime(datetime.datetime.now().timetuple()))
    week_list = []
    inbound_lst = []
    outbound_lst = []
    stock_lst = []
    l = 0
    for i in range(start_timestamp,now_timestamp+one_week,one_week):
        time_normal = time.gmtime(i+28800)  # 转换为普通时间格式（时间数组）需要加8个小时
        dt = time.strftime("%m-%d", time_normal)  # 格式化为需要的格式
        week_list.append(dt)
        k = 0
        for m in res_inbound:
            if i<=int(m[2])<=i+one_week:
                k += m[1]
        inbound_lst.append(k)
        j = 0
        for n in res_invoice:
            if i<=int(n[2])<=i+one_week:
                j -= int(n[1])
        outbound_lst.append(j)
        l+=k
        l+=j
        stock_lst.append(l)
        k = 0
        j = 0
    # print(week_list)
    # print(inbound_lst)
    # print(outbound_lst)
    # print(stock_lst)
    msg = {
        "code": 20000,
        "data":{
            "week_list":week_list,
            "inbound_lst":inbound_lst,
            "outbound_lst":outbound_lst,
            "stock_lst":stock_lst,
        },
        "message": "success",
    }
    return JsonResponse(msg, safe=False)